package sql;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.annotation.Nullable;
import java.util.ArrayList;
import java.util.List;
import org.jaaksi.commondemo.model.Person;

/**
 * 创建时间：2017年12月28日15:05 <br>
 * 作者：fuchaoyang <br>
 * 描述：
 */

public class SqlActivity extends Activity {

  private SqlHelper mSqlHelper;
  private SQLiteDatabase mDb;

  @Override protected void onCreate(@Nullable Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    mSqlHelper = new SqlHelper(this);
    // 数据库实际上是没有被创建或者打开的，直到getWritableDatabase() 或者 getReadableDatabase() 方法中的一个被调用时才会进行创建或者打开
    mDb = mSqlHelper.getWritableDatabase();
    //insert();
    //delete();
    //update();
    //select();
  }

  void insert() {
    // insert into $table_name (key,key...) values (values,values...)
    for (int i = 0; i < 20; i++) {
      DBManager.execSQL(mDb, "insert into %s (name) values ('李四%d')", SqlHelper.TABLE_PERSON, i);
    }
  }

  void delete() {
    //mDb.delete("Person", "name=?", new String[] { "李六" });
    DBManager.execSQL(mDb, "delete from %s where name='李四'", SqlHelper.TABLE_PERSON);
  }

  void update() {
    // update $table_name set key=value,key=value where ...
    DBManager.execSQL(mDb, "update %s set name='张小山' where name like '张%'", SqlHelper.TABLE_PERSON);
  }

  /**
   * select * from 表名 where 条件子句 group by 分组字句 having ... order by 排序子句 (顺序一定不能错)
   */
  void select() {
    // //将所有的SQL语句都组织到一个字符串中，使用占位符代替实际参数（selectionArgs） // and name like '%四'"
    Cursor cursor = mDb.rawQuery(
        String.format("select * from %s where name like ? and name like ?", SqlHelper.TABLE_PERSON),
        new String[] { "李%", "%四" });

    // select name, sum(salary) from company group by name order by name;

    // 按name分组，选择分组成员>2的
    //  select * from company group by name having count(name) > 2;
    List<Person> list = null;
    if (cursor != null) {
      list = new ArrayList<>();
      while (cursor.moveToNext()) {
        list.add(new Person(cursor.getString(cursor.getColumnIndex("id")),
            cursor.getString(cursor.getColumnIndex("name"))));
      }
    }
    System.out.println("list = " + (list != null ? list.toString() : null));
  }

  @Override protected void onDestroy() {
    super.onDestroy();
    mDb.close();
  }
}
